import mysql.connector
from mysql.connector import Error, pooling
from typing import Dict, List, Any, Optional, Tuple
import os
from ..config import settings

# 创建数据库连接池
try:
    DB_POOL = pooling.MySQLConnectionPool(
        pool_name="course_eval_pool",
        pool_size=5,
        pool_reset_session=True,
        **settings.db_config
    )
    print("数据库连接池创建成功")
except Error as e:
    print(f"数据库连接池创建失败: {e}")
    DB_POOL = None

def get_db_connection():
    """从连接池获取数据库连接"""
    try:
        if DB_POOL:
            return DB_POOL.get_connection()
        else:
            # 备用方案：直接连接
            return mysql.connector.connect(**settings.db_config)
    except Error as e:
        print(f"数据库连接错误: {e}")
        return None

def execute_query(query: str, params: tuple = None) -> List[Dict[str, Any]]:
    """执行SQL查询"""
    conn = get_db_connection()
    result = []
    
    if conn:
        try:
            cursor = conn.cursor(dictionary=True)
            cursor.execute(query, params or ())
            result = cursor.fetchall()
            cursor.close()
        except Error as e:
            print(f"查询执行错误: {e}")
            raise Exception(f"数据库查询失败: {str(e)}")
        finally:
            conn.close()
    else:
        raise Exception("无法获取数据库连接")
    
    return result

def execute_update(query: str, params: tuple = None) -> Optional[int]:
    """执行SQL更新（插入、更新、删除）"""
    conn = get_db_connection()
    affected_rows = None
    
    if conn:
        try:
            cursor = conn.cursor()
            cursor.execute(query, params or ())
            conn.commit()
            affected_rows = cursor.rowcount
            cursor.close()
        except Error as e:
            conn.rollback()
            print(f"更新执行错误: {e}")
            raise Exception(f"数据库更新失败: {str(e)}")
        finally:
            conn.close()
    else:
        raise Exception("无法获取数据库连接")
    
    return affected_rows

def call_procedure(proc_name: str, params: tuple = None) -> Tuple[List[Dict[str, Any]], List[Any]]:
    """调用存储过程"""
    conn = get_db_connection()
    results = []
    output_params = []
    
    if conn:
        try:
            cursor = conn.cursor(dictionary=True)
            cursor.callproc(proc_name, params or ())
            
            # 收集结果集
            for result in cursor.stored_results():
                results = result.fetchall()
            
            # 获取输出参数（如果有）
            if params:
                # 对于存储过程中的OUT参数，需要在call之后获取
                # 这里简化处理，仅获取第一个结果集
                output_params = list(params)
            
            conn.commit()
            cursor.close()
        except Error as e:
            conn.rollback()
            print(f"存储过程调用错误: {e}")
            raise Exception(f"存储过程调用失败: {str(e)}")
        finally:
            conn.close()
    else:
        raise Exception("无法获取数据库连接")
    
    return results, output_params

def validate_connection():
    """验证数据库连接"""
    try:
        conn = get_db_connection()
        if conn:
            cursor = conn.cursor()
            cursor.execute("SELECT 1")
            cursor.fetchone()
            cursor.close()
            conn.close()
            return True
    except Exception as e:
        print(f"数据库连接验证失败: {e}")
    return False 